#!/usr/bin/env python
# -*- coding: utf-8 -*-

from pojo import DevData
from utils import SQLiteDB

class DbExecutor:
    """
    封装所有的数据库操作
    """
    def __init__(self, db_path: str):
        self.db = SQLiteDB(db_path)

    def connect(self):
        """
        连接数据库
        """
        self.db.connect()

    @staticmethod
    def __get_dev_data_table_name(dev_id: int, type: str):
        """
        获取设备数据表名
        :param dev_id: 设备id
        :param type: 设备类型
        :return: 存储数据表名称
        """
        if type is None:
            table_name = f"dev_data_{dev_id}"
        else:
            table_name = f"dev_data_{dev_id}_{type}"
        return table_name

    def create_dev_data_table(self, dev_id: int, type: str):
        """
        创建设备数据表
        :param dev_id: 设备id
        :param type: 设备类型
        :return: 存储数据表名称
        """
        table_name = self.__get_dev_data_table_name(dev_id, type)
        drop_sql = f"DROP TABLE IF EXISTS {table_name}"
        self.db.execute_non_query(drop_sql)
        create_sql = f"""
        create table {table_name} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            data_id INTEGER,
            dev_id INTEGER,
            reg_id INTEGER,
            data_value REAL,
            data_time TEXT
        )
        """
        self.db.execute_non_query(create_sql)
        return table_name

    def query_dev_data(self, dev_id: int):
        """
        查询设备原始数据
        :param dev_id: 设备id
        :return: 设备数据列表
        """
        query_sql = f"""
        select a.data_time,max(a.data_value1,a.data_value2) data_value,b.class_name dev_type
             ,b.dict_name dev_name,d.dict_name dev_type2,c.dict_name reg_name,a.id data_id,b.id dev_id,c.id reg_id
        from data a
        left join DictSmallClass b on a.device_id = b.id
        left join DictSmallClass c on c.id = b.field1
        left join DictSmallClass d on d.dict_code = b.field2
        where b.id = {dev_id}
        order by data_time
        """
        return self.db.execute_query(query_sql)

    def insert_dev_data(self, dev_id: int, type: str, dev_data: DevData):
        """
        插入设备数据
        :param dev_id: 设备id
        :param type: 设备类型
        :param dev_data: 设备数据
        :return:
        """
        table_name = self.__get_dev_data_table_name(dev_id, type)
        insert_sql = f"""
            INSERT INTO {table_name} (data_id, dev_id, reg_id, data_value, data_time)
            VALUES (?, ?, ?, ?, ?)
            """
        params = (
            dev_data.data_id,
            dev_data.dev_id,
            dev_data.reg_id,
            dev_data.data_value,
            dev_data.data_time
        )
        self.db.execute_non_query(insert_sql, params)

    def query_mult_dimension_dev_data(self):
        """
        查询设备原始数据
        :return: 设备数据列表
        """
        query_sql = f"""
        WITH BaseData AS (
            SELECT
                a.data_time,
                a.data_value jia_wan,
                c.data_value wen_du,
                d.data_value yi_yang,
                e.data_value feng_su,
                f.data_value su_du,
                xc267.data_value xing_cheng_267
            FROM dev_data_484_rec a
            LEFT JOIN dev_data_485_rec c ON c.data_time = a.data_time
            LEFT JOIN dev_data_487_rec d ON d.data_time = a.data_time
            LEFT JOIN dev_data_493_rec e ON e.data_time = a.data_time
            LEFT JOIN dev_data_329_rec f ON f.data_time = a.data_time
            LEFT JOIN dev_data_267_rec xc267 ON xc267.data_time = a.data_time
        ),
        OneData AS (
            SELECT
                a.data_time
                ,y68.data_value ya_li_68,y69.data_value ya_li_69,y70.data_value ya_li_70
                ,y71.data_value ya_li_71,y72.data_value ya_li_72,y73.data_value ya_li_73,y74.data_value ya_li_74,y75.data_value ya_li_75
                ,y76.data_value ya_li_76,y77.data_value ya_li_77,y78.data_value ya_li_78,y79.data_value ya_li_79,y80.data_value ya_li_80
                ,y81.data_value ya_li_81,y82.data_value ya_li_82,y83.data_value ya_li_83,y84.data_value ya_li_84,y85.data_value ya_li_85
                ,y86.data_value ya_li_86,y87.data_value ya_li_87,y88.data_value ya_li_88,y89.data_value ya_li_89,y90.data_value ya_li_90
                ,y91.data_value ya_li_91,y92.data_value ya_li_92,y93.data_value ya_li_93,y94.data_value ya_li_94,y95.data_value ya_li_95
                ,y96.data_value ya_li_96,y97.data_value ya_li_97,y98.data_value ya_li_98,y99.data_value ya_li_99,y100.data_value ya_li_100
        
            FROM dev_data_484_rec a
                left join dev_data_68_rec y68 on a.data_time = y68.data_time
                left join dev_data_69_rec y69 on a.data_time = y69.data_time
                left join dev_data_70_rec y70 on a.data_time = y70.data_time
                left join dev_data_71_rec y71 on a.data_time = y71.data_time
                left join dev_data_72_rec y72 on a.data_time = y72.data_time
                left join dev_data_73_rec y73 on a.data_time = y73.data_time
                left join dev_data_74_rec y74 on a.data_time = y74.data_time
                left join dev_data_75_rec y75 on a.data_time = y75.data_time
                left join dev_data_76_rec y76 on a.data_time = y76.data_time
                left join dev_data_77_rec y77 on a.data_time = y77.data_time
                left join dev_data_78_rec y78 on a.data_time = y78.data_time
                left join dev_data_79_rec y79 on a.data_time = y79.data_time
                left join dev_data_80_rec y80 on a.data_time = y80.data_time
                left join dev_data_81_rec y81 on a.data_time = y81.data_time
                left join dev_data_82_rec y82 on a.data_time = y82.data_time
                left join dev_data_83_rec y83 on a.data_time = y83.data_time
                left join dev_data_84_rec y84 on a.data_time = y84.data_time
                left join dev_data_85_rec y85 on a.data_time = y85.data_time
                left join dev_data_86_rec y86 on a.data_time = y86.data_time
                left join dev_data_87_rec y87 on a.data_time = y87.data_time
                left join dev_data_88_rec y88 on a.data_time = y88.data_time
                left join dev_data_89_rec y89 on a.data_time = y89.data_time
                left join dev_data_90_rec y90 on a.data_time = y90.data_time
                left join dev_data_91_rec y91 on a.data_time = y91.data_time
                left join dev_data_92_rec y92 on a.data_time = y92.data_time
                left join dev_data_93_rec y93 on a.data_time = y93.data_time
                left join dev_data_94_rec y94 on a.data_time = y94.data_time
                left join dev_data_95_rec y95 on a.data_time = y95.data_time
                left join dev_data_96_rec y96 on a.data_time = y96.data_time
                left join dev_data_97_rec y97 on a.data_time = y97.data_time
                left join dev_data_98_rec y98 on a.data_time = y98.data_time
                left join dev_data_99_rec y99 on a.data_time = y99.data_time
                left join dev_data_100_rec y100 on a.data_time = y100.data_time
        ),
        TwoData AS (
            SELECT
                a.data_time
                ,y101.data_value ya_li_101,y102.data_value ya_li_102,y103.data_value ya_li_103,y104.data_value ya_li_104,y105.data_value ya_li_105
                 ,y106.data_value ya_li_106,y107.data_value ya_li_107,y108.data_value ya_li_108,y109.data_value ya_li_109,y110.data_value ya_li_110
                 ,y111.data_value ya_li_111,y112.data_value ya_li_112,y113.data_value ya_li_113,y114.data_value ya_li_114,y115.data_value ya_li_115
                 ,y116.data_value ya_li_116,y117.data_value ya_li_117,y118.data_value ya_li_118,y119.data_value ya_li_119,y120.data_value ya_li_120
                 ,y121.data_value ya_li_121,y122.data_value ya_li_122,y123.data_value ya_li_123,y124.data_value ya_li_124,y125.data_value ya_li_125
                 ,y126.data_value ya_li_126,y127.data_value ya_li_127,y128.data_value ya_li_128,y129.data_value ya_li_129,y130.data_value ya_li_130
                 ,y131.data_value ya_li_131,y132.data_value ya_li_132,y133.data_value ya_li_133,y134.data_value ya_li_134,y135.data_value ya_li_135
                 ,y136.data_value ya_li_136,y137.data_value ya_li_137,y138.data_value ya_li_138,y139.data_value ya_li_139,y140.data_value ya_li_140
                 ,y141.data_value ya_li_141,y142.data_value ya_li_142,y143.data_value ya_li_143,y144.data_value ya_li_144,y145.data_value ya_li_145
                 ,y146.data_value ya_li_146,y147.data_value ya_li_147,y148.data_value ya_li_148,y149.data_value ya_li_149,y150.data_value ya_li_150
                 ,y151.data_value ya_li_151,y152.data_value ya_li_152,y153.data_value ya_li_153,y154.data_value ya_li_154,y155.data_value ya_li_155
                 ,y156.data_value ya_li_156,y157.data_value ya_li_157,y158.data_value ya_li_158,y159.data_value ya_li_159,y160.data_value ya_li_160
            FROM dev_data_484_rec a
                left join dev_data_101_rec y101 on a.data_time = y101.data_time
                left join dev_data_102_rec y102 on a.data_time = y102.data_time
                left join dev_data_103_rec y103 on a.data_time = y103.data_time
                left join dev_data_104_rec y104 on a.data_time = y104.data_time
                left join dev_data_105_rec y105 on a.data_time = y105.data_time
                left join dev_data_106_rec y106 on a.data_time = y106.data_time
                left join dev_data_107_rec y107 on a.data_time = y107.data_time
                left join dev_data_108_rec y108 on a.data_time = y108.data_time
                left join dev_data_109_rec y109 on a.data_time = y109.data_time
                left join dev_data_110_rec y110 on a.data_time = y110.data_time
                left join dev_data_111_rec y111 on a.data_time = y111.data_time
                left join dev_data_112_rec y112 on a.data_time = y112.data_time
                left join dev_data_113_rec y113 on a.data_time = y113.data_time
                left join dev_data_114_rec y114 on a.data_time = y114.data_time
                left join dev_data_115_rec y115 on a.data_time = y115.data_time
                left join dev_data_116_rec y116 on a.data_time = y116.data_time
                left join dev_data_117_rec y117 on a.data_time = y117.data_time
                left join dev_data_118_rec y118 on a.data_time = y118.data_time
                left join dev_data_119_rec y119 on a.data_time = y119.data_time
                left join dev_data_120_rec y120 on a.data_time = y120.data_time
                left join dev_data_121_rec y121 on a.data_time = y121.data_time
                left join dev_data_122_rec y122 on a.data_time = y122.data_time
                left join dev_data_123_rec y123 on a.data_time = y123.data_time
                left join dev_data_124_rec y124 on a.data_time = y124.data_time
                left join dev_data_125_rec y125 on a.data_time = y125.data_time
                left join dev_data_126_rec y126 on a.data_time = y126.data_time
                left join dev_data_127_rec y127 on a.data_time = y127.data_time
                left join dev_data_128_rec y128 on a.data_time = y128.data_time
                left join dev_data_129_rec y129 on a.data_time = y129.data_time
                left join dev_data_130_rec y130 on a.data_time = y130.data_time
                left join dev_data_131_rec y131 on a.data_time = y131.data_time
                left join dev_data_132_rec y132 on a.data_time = y132.data_time
                left join dev_data_133_rec y133 on a.data_time = y133.data_time
                left join dev_data_134_rec y134 on a.data_time = y134.data_time
                left join dev_data_135_rec y135 on a.data_time = y135.data_time
                left join dev_data_136_rec y136 on a.data_time = y136.data_time
                left join dev_data_137_rec y137 on a.data_time = y137.data_time
                left join dev_data_138_rec y138 on a.data_time = y138.data_time
                left join dev_data_139_rec y139 on a.data_time = y139.data_time
                left join dev_data_140_rec y140 on a.data_time = y140.data_time
                left join dev_data_141_rec y141 on a.data_time = y141.data_time
                left join dev_data_142_rec y142 on a.data_time = y142.data_time
                left join dev_data_143_rec y143 on a.data_time = y143.data_time
                left join dev_data_144_rec y144 on a.data_time = y144.data_time
                left join dev_data_145_rec y145 on a.data_time = y145.data_time
                left join dev_data_146_rec y146 on a.data_time = y146.data_time
                left join dev_data_147_rec y147 on a.data_time = y147.data_time
                left join dev_data_148_rec y148 on a.data_time = y148.data_time
                left join dev_data_149_rec y149 on a.data_time = y149.data_time
                left join dev_data_150_rec y150 on a.data_time = y150.data_time
                left join dev_data_151_rec y151 on a.data_time = y151.data_time
                left join dev_data_152_rec y152 on a.data_time = y152.data_time
                left join dev_data_153_rec y153 on a.data_time = y153.data_time
                left join dev_data_154_rec y154 on a.data_time = y154.data_time
                left join dev_data_155_rec y155 on a.data_time = y155.data_time
                left join dev_data_156_rec y156 on a.data_time = y156.data_time
                left join dev_data_157_rec y157 on a.data_time = y157.data_time
                left join dev_data_158_rec y158 on a.data_time = y158.data_time
                left join dev_data_159_rec y159 on a.data_time = y159.data_time
                left join dev_data_160_rec y160 on a.data_time = y160.data_time
        ),
        ThreeData AS (
            SELECT
                a.data_time
                 ,y161.data_value ya_li_161,y162.data_value ya_li_162,y163.data_value ya_li_163,y164.data_value ya_li_164,y165.data_value ya_li_165
                 ,y166.data_value ya_li_166,y167.data_value ya_li_167,y168.data_value ya_li_168,y169.data_value ya_li_169,y170.data_value ya_li_170
                 ,y171.data_value ya_li_171,y172.data_value ya_li_172,y173.data_value ya_li_173,y174.data_value ya_li_174,y175.data_value ya_li_175
                 ,y176.data_value ya_li_176,y177.data_value ya_li_177,y178.data_value ya_li_178,y179.data_value ya_li_179,y180.data_value ya_li_180
                 ,y181.data_value ya_li_181,y182.data_value ya_li_182,y183.data_value ya_li_183,y184.data_value ya_li_184,y185.data_value ya_li_185
                 ,y186.data_value ya_li_186,y187.data_value ya_li_187,y188.data_value ya_li_188,y189.data_value ya_li_189,y190.data_value ya_li_190
                 ,y191.data_value ya_li_191,y192.data_value ya_li_192,y193.data_value ya_li_193,y194.data_value ya_li_194,y195.data_value ya_li_195
                 ,y196.data_value ya_li_196,y197.data_value ya_li_197,y198.data_value ya_li_198
            FROM dev_data_484_rec a
                left join dev_data_161_rec y161 on a.data_time = y161.data_time
                left join dev_data_162_rec y162 on a.data_time = y162.data_time
                left join dev_data_163_rec y163 on a.data_time = y163.data_time
                left join dev_data_164_rec y164 on a.data_time = y164.data_time
                left join dev_data_165_rec y165 on a.data_time = y165.data_time
                left join dev_data_166_rec y166 on a.data_time = y166.data_time
                left join dev_data_167_rec y167 on a.data_time = y167.data_time
                left join dev_data_168_rec y168 on a.data_time = y168.data_time
                left join dev_data_169_rec y169 on a.data_time = y169.data_time
                left join dev_data_170_rec y170 on a.data_time = y170.data_time
                left join dev_data_171_rec y171 on a.data_time = y171.data_time
                left join dev_data_172_rec y172 on a.data_time = y172.data_time
                left join dev_data_173_rec y173 on a.data_time = y173.data_time
                left join dev_data_174_rec y174 on a.data_time = y174.data_time
                left join dev_data_175_rec y175 on a.data_time = y175.data_time
                left join dev_data_176_rec y176 on a.data_time = y176.data_time
                left join dev_data_177_rec y177 on a.data_time = y177.data_time
                left join dev_data_178_rec y178 on a.data_time = y178.data_time
                left join dev_data_179_rec y179 on a.data_time = y179.data_time
                left join dev_data_180_rec y180 on a.data_time = y180.data_time
                left join dev_data_181_rec y181 on a.data_time = y181.data_time
                left join dev_data_182_rec y182 on a.data_time = y182.data_time
                left join dev_data_183_rec y183 on a.data_time = y183.data_time
                left join dev_data_184_rec y184 on a.data_time = y184.data_time
                left join dev_data_185_rec y185 on a.data_time = y185.data_time
                left join dev_data_186_rec y186 on a.data_time = y186.data_time
                left join dev_data_187_rec y187 on a.data_time = y187.data_time
                left join dev_data_188_rec y188 on a.data_time = y188.data_time
                left join dev_data_189_rec y189 on a.data_time = y189.data_time
                left join dev_data_190_rec y190 on a.data_time = y190.data_time
                left join dev_data_191_rec y191 on a.data_time = y191.data_time
                left join dev_data_192_rec y192 on a.data_time = y192.data_time
                left join dev_data_193_rec y193 on a.data_time = y193.data_time
                left join dev_data_194_rec y194 on a.data_time = y194.data_time
                left join dev_data_195_rec y195 on a.data_time = y195.data_time
                left join dev_data_196_rec y196 on a.data_time = y196.data_time
                left join dev_data_197_rec y197 on a.data_time = y197.data_time
                left join dev_data_198_rec y198 on a.data_time = y198.data_time
        ),
        FourData AS (
            SELECT
                a.data_time,
                xc267.data_value xing_cheng_267
            FROM dev_data_484_rec a
            LEFT JOIN dev_data_267_rec xc267 ON xc267.data_time = a.data_time
        )
        SELECT
            b.data_time,b.jia_wan, b.wen_du, b.yi_yang, b.feng_su,b.su_du,
            d1.ya_li_68, d1.ya_li_69, d1.ya_li_70,
            d1.ya_li_71, d1.ya_li_72, d1.ya_li_73, d1.ya_li_74, d1.ya_li_75, d1.ya_li_76, d1.ya_li_77, d1.ya_li_78, d1.ya_li_79, d1.ya_li_80,
            d1.ya_li_81, d1.ya_li_82, d1.ya_li_83, d1.ya_li_84, d1.ya_li_85, d1.ya_li_86, d1.ya_li_87, d1.ya_li_88, d1.ya_li_89, d1.ya_li_90,
            d1.ya_li_91, d1.ya_li_92, d1.ya_li_93, d1.ya_li_94, d1.ya_li_95, d1.ya_li_96, d1.ya_li_97, d1.ya_li_98, d1.ya_li_99, d1.ya_li_100,
            d2.ya_li_101, d2.ya_li_102, d2.ya_li_103, d2.ya_li_104, d2.ya_li_105, d2.ya_li_106, d2.ya_li_107, d2.ya_li_108, d2.ya_li_109, d2.ya_li_110,
            d2.ya_li_111, d2.ya_li_112, d2.ya_li_113, d2.ya_li_114, d2.ya_li_115, d2.ya_li_116, d2.ya_li_117, d2.ya_li_118, d2.ya_li_119, d2.ya_li_120,
            d2.ya_li_121, d2.ya_li_122, d2.ya_li_123, d2.ya_li_124, d2.ya_li_125, d2.ya_li_126, d2.ya_li_127, d2.ya_li_128, d2.ya_li_129, d2.ya_li_130,
            d2.ya_li_131, d2.ya_li_132, d2.ya_li_133, d2.ya_li_134, d2.ya_li_135, d2.ya_li_136, d2.ya_li_137, d2.ya_li_138, d2.ya_li_139, d2.ya_li_140,
            d2.ya_li_141, d2.ya_li_142, d2.ya_li_143, d2.ya_li_144, d2.ya_li_145, d2.ya_li_146, d2.ya_li_147, d2.ya_li_148, d2.ya_li_149, d2.ya_li_150,
            d2.ya_li_151, d2.ya_li_152, d2.ya_li_153, d2.ya_li_154, d2.ya_li_155, d2.ya_li_156, d2.ya_li_157, d2.ya_li_158, d2.ya_li_159, d2.ya_li_160,
            d3.ya_li_161, d3.ya_li_162, d3.ya_li_163, d3.ya_li_164, d3.ya_li_165, d3.ya_li_166, d3.ya_li_167, d3.ya_li_168, d3.ya_li_169, d3.ya_li_170,
            d3.ya_li_171, d3.ya_li_172, d3.ya_li_173, d3.ya_li_174, d3.ya_li_175, d3.ya_li_176, d3.ya_li_177, d3.ya_li_178, d3.ya_li_179, d3.ya_li_180,
            d3.ya_li_181, d3.ya_li_182, d3.ya_li_183, d3.ya_li_184, d3.ya_li_185, d3.ya_li_186, d3.ya_li_187, d3.ya_li_188, d3.ya_li_189, d3.ya_li_190,
            d3.ya_li_191, d3.ya_li_192, d3.ya_li_193, d3.ya_li_194, d3.ya_li_195, d3.ya_li_196, d3.ya_li_197, d3.ya_li_198,
            d4.xing_cheng_267
        FROM BaseData b
        LEFT JOIN OneData d1 ON b.data_time = d1.data_time
        LEFT JOIN TwoData d2 ON b.data_time = d2.data_time
        LEFT JOIN ThreeData d3 ON b.data_time = d3.data_time
        LEFT JOIN FourData d4 ON b.data_time = d4.data_time
        """
        return self.db.execute_query(query_sql)


    def close(self):
        """
        关闭数据库连接
        """
        self.db.close()
